﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Microsoft.Data.SqlClient;
using EasyDater.Base;
using Microsoft.IdentityModel.Tokens;
using EasyDater.Helper;

namespace EasyDater.Built
{

    //自动获取表结构信息，组合SQL语句进行操作
    public class BuiltSQL
    {

        BuiltCore builtCore = new BuiltCore();
       
        #region 向单独一个表中插入数据
        /// <summary>
        /// 向单独一个表中插入数据
        /// </summary>
        /// <param name="mainTable">表名</param>
        /// <param name="sdList">往表中将要插入的数据 [key,value]</param>  
        /// <returns>成功与否的标志</returns>
        public bool InsertTable(string tableName, SortedDictionary<string, string> sdList)
        {
            string strMainSQL = builtCore.GetInsertSql(tableName, sdList);
            bool flag = false;
            try
            {
                flag = SQLHelper.ExecuteSql(strMainSQL);
            }
            catch (Exception e)
            {
                // LogHelper.WriteLog("添加数据失败:" + strMainSQL, e);
            }
            return flag;
        }
        // 向单独一个表中插入数据，附带执行其他SQL语句
        public bool InsertTable(string tableName, SortedDictionary<string, string> sdList, string sql)
        {
            string strMainSQL = builtCore.GetInsertSql(tableName, sdList);
            strMainSQL += sql;

            bool flag = false;
            try
            {
                flag = SQLHelper.ExecuteSqlWithTransaction(strMainSQL);
            }
            catch (Exception e)
            {
                // LogHelper.WriteLog("添加数据失败:" + strMainSQL, e);
            }
            return flag;

        }
   
        /// <summary>
        /// 向表中插入一条数据， 同时执行其它一条或多条SQL语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="sdList">需要插入的数据</param>
        /// <param name="arrayList">同时需要执行的SQL语句</param>
        /// <returns></returns>
        public bool InsertTable(string tableName, SortedDictionary<string, string> sdList, ArrayList arrayList)
        {
            string strMainSQL = builtCore.GetInsertSql(tableName, sdList);
            if (arrayList is not null)
            {
                for (int i = 0; i < arrayList.Count; i++)
                {
                    string sql = arrayList[i].ToString().Trim();
                    strMainSQL += sql;
                    if (!sql.EndsWith(";"))
                        strMainSQL += ";";
                }
            }
            bool flag = false;
            try
            {
                flag = SQLHelper.ExecuteSqlWithTransaction(strMainSQL);
            }
            catch (Exception e)
            {
                //  LogHelper.WriteLog("添加数据失败:" + strMainSQL, e);
            }
            return flag;

        }
        #endregion


        #region 同时插入多张表
        //
        public bool InsertTable(string tableName, List<SortedDictionary<string, string>> list)
        {
            if (list == null) return false;
            int count = list.Count;
            if (count < 1) return false;

            string strMainSQL = "";
            for (int j = 0; j < count; j++)
            {
                strMainSQL += builtCore.GetInsertSql(tableName, list[j]);
            }

            bool flag = false;
            try
            {
                flag = SQLHelper.ExecuteSql(strMainSQL);
            }
            catch (Exception e)
            {
                // LogHelper.WriteLog("添加数据失败:" + strMainSQL, e);
            }
            return flag;
        }

        public bool InsertTable(string tableName, List<SortedDictionary<string, string>> list, string sql)
        {
            if (list == null) return false;
            int count = list.Count;
            if (count < 1) return false;

            string strMainSQL = "";
            for (int j = 0; j < count; j++)
            {
                strMainSQL += builtCore.GetInsertSql(tableName, list[j]);
            }
            strMainSQL += sql;

            bool flag = false;
            try
            {
                flag = SQLHelper.ExecuteSql(strMainSQL);
            }
            catch (Exception e)
            {
                //  LogHelper.WriteLog("添加数据失败:" + strMainSQL, e);
            }
            return flag;
        }
        public bool InsertTable(string tableName, List<SortedDictionary<string, string>> list, ArrayList arrayList)
        {
            if (list == null) return false;
            int count = list.Count;
            if (count < 1) return false;

            string strMainSQL = "";
            for (int j = 0; j < count; j++)
            {
                strMainSQL += builtCore.GetInsertSql(tableName, list[j]);
            }
            for (int i = 0; i < arrayList.Count; i++)
            {
                string sql = arrayList[i].ToString().Trim();
                strMainSQL += sql;
                if (!sql.EndsWith(";"))
                    strMainSQL += ";";
            }

            bool flag = false;
            try
            {
                flag = SQLHelper.ExecuteSql(strMainSQL);
            }
            catch (Exception e)
            {
                //LogHelper.WriteLog("添加数据失败:" + strMainSQL, e);
            }
            return flag;
        }

        #endregion







        #region 删除一条记录

        public bool DeleteTable(string tableName, string primaryKey, int primaryValue)
        {
            string strMainSQL = "Delete " + tableName + " WHERE " + primaryKey + "=" + primaryValue;
            return SQLHelper.ExecuteSql(strMainSQL);
        }
        public bool DeleteTable(string tableName, string primaryKey, string primaryValue)
        {
            string strMainSQL = "Delete " + tableName + " WHERE " + primaryKey + "='" + primaryValue + "'";
            return SQLHelper.ExecuteSql(strMainSQL);
        }


        public bool DeleteTable(string tableName, string primaryKey, int primaryValue, string sql)
        {
            string strMainSQL = "Delete " + tableName + " WHERE " + primaryKey + "=" + primaryValue;
            strMainSQL += ";" + sql;
            return SQLHelper.ExecuteSqlWithTransaction(strMainSQL);

        }
        public bool DeleteTable(string tableName, string primaryKey, string primaryValue, string sql)
        {
            string strMainSQL = "Delete " + tableName + " WHERE " + primaryKey + "='" + primaryValue + "'";
            strMainSQL += ";" + sql;
            return SQLHelper.ExecuteSqlWithTransaction(strMainSQL);

        }
        public bool DeleteTable(string tableName, string primaryKey, int primaryValue, ArrayList arrayList)
        {
            string strMainSQL = "Delete " + tableName + " WHERE " + primaryKey + "=" + primaryValue + ";";
            for (int i = 0; i < arrayList.Count; i++)
            {
                string sql = arrayList[i].ToString().Trim();
                strMainSQL += sql;
                if (!sql.EndsWith(";"))
                    strMainSQL += "; ";
            }
            return SQLHelper.ExecuteSqlWithTransaction(strMainSQL);

        }
        /// <summary>
        /// 删除 被自己定义的业务逻辑层调用
        /// 带其它SQL语句同时操作
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="primaryKey">主键 new string[] { "Id",id, "N" };</param>
        /// <param name="arrayList">其它完整SQL语句</param>
        /// <returns></returns>
        public bool DeleteTable(string tableName, string primaryKey, string primaryValue, ArrayList arrayList)
        {
            string strMainSQL = "Delete " + tableName + " WHERE " + primaryKey + "='" + primaryValue + "';";
            if (arrayList is not null)
            {
                for (int i = 0; i < arrayList.Count; i++)
                {
                    string sql = arrayList[i].ToString().Trim();
                    strMainSQL += sql;
                    if (!sql.EndsWith(";"))
                        strMainSQL += "; ";
                }
            }
            return SQLHelper.ExecuteSqlWithTransaction(strMainSQL);

        }
        #endregion


        #region  更新一个表中数据
        /// <summary>
        /// 更新一个表中插入数据
        /// </summary>
        /// <param name="mainTable">表名</param>
        /// <param name="mainList">将要更新的数据 [key,value,type]</param>
        /// <param name="primaryKey">主键 [主键][值][类型]</param>
        /// S: 字符串 N：整数 D：decimal数字 F：浮点数 A：日期
        /// <returns></returns>

        public bool UpdateTable(string tableName, SortedDictionary<string, string> sdList)
        {

            string strMainSQL = builtCore.GetUpdateSql(tableName, sdList);// 
            bool flag = false;
            try
            {
                flag = SQLHelper.ExecuteSql(strMainSQL);
            }
            catch (Exception ex)
            {
                //LogHelper.WriteLog("更新数据失败:" + strMainSQL, ex);
            }
            return flag;
        }
        public bool UpdateTable(string tableName, SortedDictionary<string, string> sdList, ArrayList arrayList)
        {
            string strMainSQL = builtCore.GetUpdateSql(tableName, sdList);
            for (int i = 0; i < arrayList.Count; i++)
            {
                string sql = arrayList[i].ToString().Trim();
                strMainSQL += sql;
                if (!sql.EndsWith(";"))
                    strMainSQL += ";";
            }

            bool flag = false;
            try
            {
                flag = SQLHelper.ExecuteSqlWithTransaction(strMainSQL);
            }
            catch (Exception e)
            {
                //LogHelper.WriteLog("添加数据失败:" + strMainSQL, e);
            }
            return flag;

        }

      
        #endregion

        public string InsertMultiTable(List<string[,]> detailList)
        {
            string result = "";
            string strDetailSQL = "";
            int count = detailList.Count;
            for (int j = 0; j < count; j++)
            {
                string subKey = "";
                string subValue = "";
                string[,] item = detailList[j];


                for (int i = 1; i < item.GetLength(0); i++)
                {

                    subKey += item[i, 0].ToString();//键
                    ////N是整数 D是decimal
                    if (item[i, 2].ToString() == "N")//类型
                        subValue += item[i, 1].ToString();//值
                    else if (item[i, 2].ToString() == "D")
                        subValue += item[i, 1].ToString();//值
                    else if (item[i, 2].ToString() == "A")//date 的第二个字母
                        subValue += "'" + item[i, 1].ToString() + "'";//值
                    else
                        subValue += "'" + item[i, 1].Trim() + "'";
                    if (i < item.GetLength(0) - 1)
                    {
                        subKey += ",";
                        subValue += ",";
                    }
                }
                strDetailSQL += " INSERT INTO " + item[0, 1].ToString() + " (" + subKey + ") VALUES (" + subValue + ")";
                if (j < count - 1)
                    strDetailSQL += ";";
            }



            try
            {
                SQLHelper.ExecuteSql(strDetailSQL);
                result = "执行成功";


            }
            catch (Exception ex)
            {
                result = ex.Message.ToString();
            }
            return result;
        }

        #region 同时插入主表和明细表数据
        /// <summary>
        /// 同时插入主表和明细表数据
        /// </summary>
        /// <param name="mainTable">主表的表名</param>
        /// <param name="mainList">往主表中将要插入的数据 [key,value,type]</param>
        /// <param name="detailTable">从表的表名</param>
        /// <param name="detailList">往从表中插入的数据 []</param>
        /// <returns></returns>
        public string InsertMainAndDetail(string mainTable, SortedDictionary<string, string> mainList, string detailTable, List<SortedDictionary<string, string>> detailList)
        {

            //第一步插入主表数据
            string result = "";
            try
            {
                string strMainSQL = builtCore.GetInsertSql(mainTable, mainList);
                //组合从表的SQL语句####################################
                string strDetailSQL = "";
                int count = detailList.Count;
                for (int j = 0; j < count; j++)
                {
                    strDetailSQL += builtCore.GetInsertSql(detailTable, detailList[j]);
                }

                try
                {
                    if (SQLHelper.ExecuteSql(strMainSQL + "" + strDetailSQL))
                        result = "执行成功";
                    else
                        result = "执行失败";
                }
                catch (Exception ex)
                {
                    result = ex.Message.ToString();
                }
            }
            catch (Exception ex)
            {
                result = ex.Message.ToString();
            }
            return result;

        }

        #endregion


        #region 同时更新主表和明细表
        public string UpdateMainAndDetail(string mainTable, SortedDictionary<string, string> mainList, string[] primaryKey, string detailTable, List<SortedDictionary<string, string>> detailList)
        {

            //1:组合更新主表的SQL语句
            mainList.Add(primaryKey[0], primaryKey[1]);//更新条件
            string result = "";
            string strMainSQL = builtCore.GetUpdateSql(mainTable, mainList);
            //2:组合子表的SQL语句
            string strDetailSQL = "";
            int count = detailList.Count;
            for (int j = 0; j < count; j++)
            {
                strDetailSQL += builtCore.GetInsertSql(detailTable, detailList[j]);
            }
            //3:组合删除从表SQL语句-----先删除旧数据，然后插入新数据
            string strOldSQL = "delete " + detailTable + " WHERE " + primaryKey[0] + "=";
            if (primaryKey[2] == "N" || primaryKey[2] == "D")
                strOldSQL += primaryKey[1];
            else
                strOldSQL += "'" + primaryKey[1] + "'";

            //4：执行SQL语句（带有事务）
            try
            {
                if (SQLHelper.ExecuteSql(strMainSQL + " " + strOldSQL + ";" + strDetailSQL))
                    result = "执行成功";
                else
                    result = "执行失败";

            }
            catch (Exception ex)
            {
                result = ex.Message;
            }
            return result;
        }
        #endregion

       

        #region
        //获取分页数据，返回datatable
        public DataTable RunProcGetList(int CurrentPage, int RowsOfPage, string strTables, string strFields, string strFilter, string strSort, out int allRows)
        {
            //@CurrentPage int,--当前页码
            //@RowsPerPage int,--每页显示的条数
            //@Tables  varchar(500),--表名称或视图名称
            //@Fields  varchar(1000) = '*',--选择表的哪些字段
            //@Filter  Varchar(500),--Where条件语句,不带Where
            //@Sort  varchar(200) = NULL,--排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
            //--@HowManyRows Int OUTPUT--总共有多少条数
            SqlParameter pCurrentPage = new SqlParameter("@CurrentPage", SqlDbType.Int);
            pCurrentPage.Value = CurrentPage;

            SqlParameter pRowsPerPage = new SqlParameter("@RowsPerPage", SqlDbType.Int);
            pRowsPerPage.Value = RowsOfPage;

            SqlParameter pTables = new SqlParameter("@Tables", SqlDbType.VarChar, 500);
            pTables.Value = strTables;

            SqlParameter pFields = new SqlParameter("@Fields", SqlDbType.VarChar, 1000);
            pFields.Value = strFields;
            //检索信息的条件
            SqlParameter pFilter = new SqlParameter("@Filter", SqlDbType.VarChar, 500);
            pFilter.Value = strFilter;

            SqlParameter pSort = new SqlParameter("@Sort", SqlDbType.VarChar, 200);
            pSort.Value = strSort;

            SqlParameter pHowManyRows = new SqlParameter("@HowManyRows", SqlDbType.Int);
            pHowManyRows.Direction = ParameterDirection.Output;

            SqlParameter[] spList = { pCurrentPage, pRowsPerPage, pTables, pFields, pFilter, pSort, pHowManyRows };

            int allRows1 = 0;
            DataTable dt = SQLHelper.ExecutePorcedureGetDataTable("pg_GetList", spList, out allRows1);
            allRows = allRows1;

            //填充空白行	
            //if (dt.Rows.Count < RowsOfPage)
            //    {
            //        for (int i = dt.Rows.Count - 1; i < RowsOfPage; i++)
            //        {
            //            DataRow dr = dt.NewRow();
            //            dt.Rows.Add(dr);
            //        }
            //    }

            return dt;

        }
        #endregion




    }
}